<?php
namespace app\admin\controller;
use think\Db;
use think\Controller;
use PHPExcel_IOFactory;
use PHPExcel;
use PHPExcel_Shared_Date;
use PHPExcel_Cell_DataType;
use app\admin\controller\ExportExcel;

class Students extends Common
{
    public function index()
    {

       $data = model("Students")->getDataAll(1);
       //全部信息
       $this->assign('data',$data);

       $major = Db::name('major')->where('status',1)->select();
	   $this->assign('major',$major);

       return $this->fetch();

    }

    //软删除
    public function del(){
    	//获取的是一个数组
    	$ids = input('post.ids/a');
    	//将数组转为字符串 以逗号分隔
    	$ids = implode(',',$ids);
    	//软删除 更新状态
    	$result = Db::name('students')->where("id in ($ids)")->setField('status',0);
    	if($result){
    		$this->success("删除成功");
    	}else{
    		$this->error("删除失败");
    	}
    }

    public function add(){
        if (request()->isPost()) {
             $data = input('post.data/a');
             // dump($data);die;
             if(!$data){
                return json(array('code'=>0,'msg'=>"参数错误"));
             }
             $data['add_time'] = date('Y-m-d H:i:s');
             $result = model('students')->addStudents($data);

             if($result){
                    return json(['code'=>1,'msg'=>'添加成功']);
             }else{
                    return json(['code'=>0,'msg'=>'添加失败']);
             }


        }else{
            $major = Db::name('major')->where('status',1)->select();
            $this->assign('major',$major);

            

            return $this->fetch();
        }
    
    }


     public function edit($id=""){
            if(request()->isPost()){
                $data = input('post.data/a');

                $result = Db::name('students')->where('id',$data['id'])->update(['username'=>$data['username'],'id_card'=>$data['id_card'],'sex'=>$data['sex'],'tel'=>$data['tel'],'password'=>$data['password'],'class_name'=>$data['class_name'],'major_id'=>$data['major_id'],'class_teacher'=>$data['class_teacher'],'address'=>$data['address']]);

                if ($result) {
                        return json(['code'=>1,'msg'=>'修改成功']);
                 }else{
                        return json(['code'=>0,'msg'=>'修改失败']);
                 }

            }else{
                $major = Db::name('major')->where('status',1)->select();
                $this->assign('major',$major);

                $students = model('Students')->editStudents($id);
                // dump($students);die;
                
                $this->assign('students',$students);

                return $this->fetch();
            }
      
    }

    ///phpexcel导出
    public function phpexcelout ()
    {
        vendor("PHPExcel.PHPExcel");
        vendor("PHPExcel.PHPExcel.Writer.Excel5");
        vendor("PHPExcel.PHPExcel.Writer.Excel2007");
        vendor("PHPExcel.PHPExcel.IOFactory");
        $objPHPExcel  = new \PHPExcel();
        for ($i = 0; $i < 2; $i++) {
            if($i > 0)
            {    
                #创建新的内置表
                $objPHPExcel->createSheet();
            }
            if ($i == 0) {
                $objPHPExcel->setActiveSheetIndex($i);
                #获取当前活动的sheet
                $objSheet = $objPHPExcel->getActiveSheet();
                #给当前活动的sheet起个名字
                $objSheet->setTitle('学生信息表');

                $data = Model('Students')->getList(1);
                // dump($data);die;
                $objSheet->setCellValue('A1','用户名')->setCellValue('B1','身份证')->setCellValue('C1','性别')->setCellValue('D1','密码')->setCellValue('E1','班级')->setCellValue('F1','老师')->setCellValue('G1','电话')->setCellValue('H1','专业')->setCellValue('I1','地址');
                
                $j = 2;
                foreach ($data as $key => $v) {
                    $objSheet->setCellValue('A'.$j,$v['username'])
                            ->setCellValue('B'.$j,$v['id_card'])
                            ->setCellValue('C'.$j,$v['sex'])
                            ->setCellValue('D'.$j,$v['password'])
                            ->setCellValue('E'.$j,$v['class_name'])
                            ->setCellValue('F'.$j,$v['class_teacher'])
                            ->setCellValue('G'.$j,$v['tel'])
                            ->setCellValue('H'.$j,$v['major_id'])
                            ->setCellValue('I'.$j,$v['address']);
                    $j++;
                }
            } else {
                $objPHPExcel->setActiveSheetIndex($i);
                #获取当前活动的sheet
                $objSheet = $objPHPExcel->getActiveSheet();
                #给当前活动的sheet起个名字
                $objSheet->setTitle('专业解析表');
                $objSheet->setCellValue('A1','专业id')->setCellValue('B1','专业名称');
                $data = db('major')->where('status',1)->select();
                $j = 2;
                foreach ($data as $k => $v) {
                    $objSheet->setCellValue('A'.$j,$v['id'])->setCellValue('B'.$j,$v['major_name']);
                    $j++;
                }
            }
        }
        
        header('Content-Type: applicationnd.ms-excel');
        header('Content-Disposition: attachment;filename="学生信息表.xls"');  
        //日期为文件名后缀
        header('Cache-Control: max-age=0');
        #生成excel文件
        $objWrite = \PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');
        $objWrite->save('php://output');
    }

    //
    public function phpexcelimport ()
    {
        vendor("PHPExcel.PHPExcel");
        vendor("PHPExcel.PHPExcel.Writer.Excel5");
        vendor("PHPExcel.PHPExcel.Writer.Excel2007");
        vendor("PHPExcel.PHPExcel.IOFactory");

        $file = request()->file('file');
        // dump($file);die;
        if($file){
            $info = $file->move(ROOT_PATH . 'public' . DS . 'uploads' . DS . 'students' . DS);
            if($info){
                // 成功上传后 获取上传信息
                $path = 'public' . DS . 'uploads' . DS . 'students' . DS;
                // 输出 20160820/42a79759f284b767dfcb2a0197904287.jpg
                $name =  $info->getSaveName();
                $path = ROOT_PATH . $path. DS .$name;
            }else{
                // 上传失败获取错误信息
                // echo $file->getError();
                $this->error('导入失败');
            }
        } else {
            $this->error('未获取到文件');
        }
        $extension = strtolower(pathinfo($path, PATHINFO_EXTENSION));//判断导入表格后缀格式
        
        if($extension == 'xlsx') {
            $objReader =\PHPExcel_IOFactory::createReader('Excel2007');
            $objPHPExcel =$objReader->load($path, $encode = 'utf-8');
        }else if($extension == 'xls'){
            $objReader =\PHPExcel_IOFactory::createReader('Excel5');
            $objPHPExcel =$objReader->load($path, $encode = 'utf-8');
        } else {
            return $this->error('文件格式错误!');
        }

        #获取Excel文件中sheet的总数
        $sheetCount = $objPHPExcel->getSheetCount();
        for ($i=0; $i < $sheetCount; $i++) { 
            $data[] = $objPHPExcel->getSheet($i)->toArray();
        }
        // dump($data);
        $data = $data[0];   //获取到的数据
        $name = [];     //字段名
        $data2 = [];    //最后插入的数据
        foreach ($data as $key => $val) {
            if ($key == 0) {    //第一个不要
                continue;
            }
            $data2[] = [
                'username'  =>  $val[0],
                'id_card'   =>  $val[1],
                'sex'       =>  $val[2],
                'password'  =>  $val[3],
                'class_name'    =>  $val[4],
                'class_teacher' =>  $val[5],
                'tel'       =>  $val[6],
                'major_id'  =>  $val[7],
                'address'   =>  $val[8],
                'img'       =>  $val[9],
            ];
        }
        $insert = db('students')->insertAll($data2);
        if ($insert) {
            $this->success('导入成功!');
        } else {
            $this->error('导入失败!');
        }
    }

}